#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2015年11月9日

@author: chenyongbing
'''
import sys,os,logging
#current_dir = os.path.dirname(__file__)
current_dir = os.path.split(os.path.realpath(__file__))[0]
import xml.etree.ElementTree as ET
import openpyxl,re
from openpyxl.styles import Font, Color,Fill,Style
from openpyxl.styles import colors
from openpyxl.cell import get_column_letter
import datetime

'''
    按照大运城报表格式 导出周数据 ;temp : ../templates/common/week_report_template_like_大运城.xlsx
'''




sys.path.append(os.path.join(current_dir,'../../'))
from base.zhimind_datas import myZhimindDatas
from base.time_method import myTimeMethod
from base.retail_datas import myRetailDatas
        
        
    
class analyzeDYCDatas():
    
    def __init__(self):
        pass


    
    def get_week_datas(self,groups=[],startTime=None,endTime=None):
        '''大运城 模板'''
        
        '''
        店铺周数据    店铺入店率    同已有口径，算周入店率
            店铺累计入店人数    7天累计入店人数（当天去重，跨天不去重）
            店铺新客户占比    同已有口径，算周平均新顾客占比
            店铺老客户占比    同已有口径，算周平均老顾客占比
            店铺日均入店顾客    同已有口径，算周平均入店人数
            店铺驻店时长    同已有口径，算周平均驻店时长
        '''
        
        datas = {}
        weekCustomerCntDatas = myZhimindDatas.get_one_target_from_rank_stats_by_group(timeType='day', groups=groups, groupType=4, st=startTime, et=endTime, target='new_cust+old_cust')
        
        
        weekCustomerDaats = myZhimindDatas.get_target_from_rank_stats_by_group(timeType='week', groups=groups, groupType=4, st=startTime, et=endTime, targetList=['ent_cust_rate','old_cust_rate','new_cust','old_cust','ent_cust_dur','ent_cust_cnt','avl_days'])
        
        for group in groups:
            group = int(group)
            if group not in weekCustomerCntDatas.keys():
                shop_week_customer_cnt = '-'
            else:
                shop_week_customer_cnt = weekCustomerCntDatas[group]
            
            
            if group not in weekCustomerDaats.keys():
                shop_week_customer_rate = '-'
                shop_week_old_customer_rate = '-'
                shop_week_new_customer_rate = '-'
                shop_week_customer_avg_cnt = '-'
                shop_week_customer_avg_dur = '-'
            else:
                ent_cust_rate = weekCustomerDaats[group]['ent_cust_rate']
                old_cust_rate = weekCustomerDaats[group]['old_cust_rate']
                new_cust = weekCustomerDaats[group]['new_cust']
                old_cust = weekCustomerDaats[group]['old_cust']
                ent_cust_dur = weekCustomerDaats[group]['ent_cust_dur']
                ent_cust_cnt = weekCustomerDaats[group]['ent_cust_cnt']
                avl_days = weekCustomerDaats[group]['avl_days']
                
                shop_week_customer_rate = ent_cust_rate
                shop_week_old_customer_rate = old_cust_rate
                if new_cust+old_cust ==0:
                    shop_week_new_customer_rate = 0
                else:
                    shop_week_new_customer_rate = round(1- shop_week_old_customer_rate,3)
                if avl_days!=0:
                    shop_week_customer_avg_cnt = int(round(new_cust+old_cust/float(avl_days),0))
                else:
                    shop_week_customer_avg_cnt = 0
                if ent_cust_cnt!=0:
                    shop_week_customer_avg_dur = int(round(ent_cust_dur/1000/60,1))
                
            datas[group] = [shop_week_customer_rate,shop_week_customer_cnt,shop_week_new_customer_rate,shop_week_old_customer_rate,
                            shop_week_customer_avg_cnt,shop_week_customer_avg_dur]
        return datas

    def get_day_cate_datas(self,groups=[],startTime=None,endTime=None):
        '''大运城 模板'''
        '''
        指标类别    指标名称    统计口径（只算日数据）
        品类日数据    分品类客户入店数    品类下所有店铺入店人数累加（入店人数去重）
            分品类店铺平均入店数    品类下所有店铺入店人数累加（不去重）÷品类下店铺数量
            分品类驻店时长    品类下所有入店客户驻店时长累加÷品类下累计入店人次
            分品类平均老客户占比    品类下所有店铺的老顾客人数（不去重）÷品类下所有店铺的顾客人数（不去重）
        '''

        dayCustomerDistCnt = myZhimindDatas.get_customer_cnt_from_mac_customer_stats( role=0, distinct=True, groups=groups, groupType=4, st=startTime, et=endTime)
        
        
        dayCustomerDatas = myZhimindDatas.get_target_from_rank_stats(timeType='day', 
                                                                     groups=groups, 
                                                                     groupType=4, 
                                                                     st=startTime, et=endTime, 
                                                                     targetList=['cast(round(sum(new_cust+old_cust)/count(any_group),0) as SIGNED)','round(sum(ent_cust_dur*ent_cust_cnt)/sum(ent_cust_cnt)/1000/60,1)','round(sum(old_cust)/sum(new_cust+old_cust),3) '])
        return [dayCustomerDistCnt]+list(dayCustomerDatas)
        
    def get_day_floor_datas(self,groups=[],secondGroups=[],startTime=None,endTime=None):
        '''大运城 模板'''
        '''
        楼层日数据    楼层累计入店数    该楼层所有店铺入店人数累加（去重）
            楼层店铺平均入店数    该楼层累计入店人数（不去重）÷该楼层店铺数量
            楼层平均驻店时长    该楼层所有入店客户的驻店时长÷该楼层累计入店人次
            楼层上楼率    一天内在该楼层以上的楼层探测到的已在该楼层出现过的客流人数（去重）÷该楼层的客流人数（去重）（如：当日在二楼及以上已在一楼出现过的客流人数÷当日一楼的客流人数）
        '''
        dayCustomerDistCnt = myZhimindDatas.get_customer_cnt_from_mac_customer_stats( role=0, distinct=True, groups=groups, groupType=4, st=startTime, et=endTime)
        
        
        dayCustomerDatas = myZhimindDatas.get_target_from_rank_stats(timeType='day', 
                                                                     groups=groups, 
                                                                     groupType=4, 
                                                                     st=startTime, et=endTime, 
                                                                     targetList=['cast(round(sum(new_cust+old_cust)/count(any_group),0) as SIGNED)','round(sum(ent_cust_dur*ent_cust_cnt)/sum(ent_cust_cnt)/1000/60,1)'])
        if secondGroups == None or len(secondGroups)==0:
            upFloorRate = '-'
        else:
            macs = myZhimindDatas.get_mac_from_customer_and_walkby(groups=groups, st=startTime, et=endTime)
            secondMacs = myZhimindDatas.get_mac_from_customer_and_walkby(groups=secondGroups, st=startTime, et=endTime)
            
            samemacs = list(set(secondMacs).intersection(set(macs)))
            if len(macs)==0:
                upFloorRate = '-'
            else:
                upFloorRate = round(float(len(samemacs))/len(macs),3)
        
        return [dayCustomerDistCnt]+list(dayCustomerDatas)+[upFloorRate]
    
    
    def get_shop_datas_coincidence_degree(self,groups=[],secondGroups=[],startTime=None,endTime=None):
        ''' 重合度    顾客重合度    累计时间段内进入过A店铺并且进入过B店铺的客户占累计时间段内A店铺入店人数的比例
        '''
        
        datas = myZhimindDatas.get_cross_customer_cnt(distinct=True, groups=groups, crossGroups=secondGroups, groupType=4, st=startTime, et=endTime)
        return datas
    
    
    
    
    def analyze_company_config(self,day=None,comp_groups=[]):
        datas = {}
        company_config_text = open('/Users/chenyongbing/Downloads/testing.xml').read()
        #company_config_text = myZhimindDatas.select_company_config(day=day)
        #print company_config_text
        root = ET.fromstring(company_config_text)
        if comp_groups==None or comp_groups==[]:
            return '',{},{},{} 
        
        shop_datas = {}
        floor_datas = {}
        cate_datas = {}
        for comp_node in root.findall('.//group[@type="0"]'):
            comp_group =  comp_node.attrib['id']
            if int(comp_group) not in comp_groups:continue
            try:
                comp_name  = comp_node.attrib['name']
            except:
                comp_name = '未知'

            
            for shop_node in comp_node.findall('.//group[@type="4"]'):
                shop_name = shop_node.attrib['name']
                shop_group = shop_node.attrib['id']
                shop_sn = shop_node.attrib['sn']
                floor_node = shop_node.find('.//param[@name="storey_floor"]')
                cate_node = shop_node.find('.//param[@name="brand_category"]')
                if floor_node==None or cate_node==None:
                    continue
                floor = floor_node.attrib['value']
                cates = cate_node.attrib['value']
                if floor == '' or cates == '':continue
                comp_id = myRetailDatas.get_comp_id(comp_group= comp_group)
                shop_brands = myRetailDatas.get_shop_brand(comp_id=comp_id,shop_group=shop_group)
                
                shop_datas[shop_group] = {'shop_group':shop_group,'shop_name':shop_name,'shop_sn':shop_sn,'shop_brands':','.join(shop_brands)}
                if floor not in floor_datas.keys():
                    floor_datas[floor] = []
                floor_datas[floor].append(shop_group)
                
                for cate in cates.split(','):
                    if cate not in cate_datas.keys():
                        cate_datas[cate] = []
                    cate_datas[cate].append(shop_group)
                
        return comp_name,shop_datas,floor_datas,cate_datas
                
                
    def write_shop_datas_to_excel(self,wb,shopInfos,datas):
        shop_page_ws = wb.get_sheet_by_name(u'店铺统计')
            
        shop_index = 2
        for shop_data in shopInfos.values():
            
            shop_group = shop_data['shop_group']
            shop_name = shop_data['shop_name']
            shop_sn = shop_data['shop_sn']
            shop_brands = shop_data['shop_brands']
            if int(shop_group) not in datas.keys():
                continue
            
            row = shop_index
            data = datas[int(shop_group)]
            newdata = [shop_name,shop_sn,shop_brands]+data
            cols = ['A','B','C','D','E','F','G','H','I']
            for i in range(len(cols)):
                shop_page_ws['%s%s'%(cols[i],row)].style = shop_page_ws['%s%s'%(cols[i],2)].style
                shop_page_ws['%s%s'%(cols[i],row)] = newdata[i]
            shop_index+=1
        if shop_index>=9:
            shop_page_ws['A%s'%(shop_index+2)] = '注1：店铺编号、品牌信息需要预先维护到店铺信息中'
            shop_page_ws['A%s'%(shop_index+3)] = '注2：店铺信息的默认统计周期为一周'
        
        
    def write_cate_datas_to_excel(self,wb,datas):
        cate_page_ws = wb.get_sheet_by_name(u'品类统计')
        start_row = 1
        start_col = 2
        feilds = ["累计入店数","店铺平均入店数","平均驻店时长","平均老顾客占比"]
        col = start_col
        cateStyle = cate_page_ws.cell('B1').style
        feildStyle = cate_page_ws.cell('B2').style 
        
        for cate_name , cate_datas in datas.items():
            row = start_row
            cate_page_ws.merge_cells(start_row=row,start_column=col,end_row=row,end_column=col+len(feilds)-1)
            
            cate_page_ws.cell(row=row,column=col).style = cateStyle
            cate_page_ws.cell(row=row,column=col).value = cate_name
            row+=1
            for i in range(len(feilds)):
                cate_page_ws.cell(row=row,column=col+i).style = feildStyle
                cate_page_ws.cell(row=row,column=col+i).value = feilds[i]
            days = cate_datas.keys()
            days.sort()
            
            for day  in days:
                cate_data = cate_datas[day]
                row+=1
                
                cate_page_ws.cell(row=row,column=1).value = day
                for j in range(len(cate_data)):
                    if col+j!=j+2:
                        cate_page_ws.cell(row=row,column=col+j).style = cate_page_ws.cell(row=row,column=j+2).style
                    cate_page_ws.cell(row=row,column=col+j).value = cate_data[j]
            row+=1
            for j in range(len(cate_data)):
                    if col+j!=j+2:
                        
                        cate_page_ws.cell(row=row,column=col+j).style = cate_page_ws.cell(row=row,column=j+2).style
                        cate_page_ws.cell(row=row,column=col+j).value = cate_page_ws.cell(row=row,column=j+2).value
            col+=len(feilds)
            
    def write_floor_datas_to_excel(self,wb,datas):
        cate_page_ws = wb.get_sheet_by_name(u'楼层统计')
        start_row = 1
        start_col = 2
        feilds = ["累计入店数","店铺平均入店数","平均驻店时长","上楼率"]
        col = start_col
        cateStyle = cate_page_ws.cell('B1').style
        feildStyle = cate_page_ws.cell('B2').style 
        for cate_name , cate_datas in datas.items():
            row = start_row
            cate_page_ws.merge_cells(start_row=row,start_column=col,end_row=row,end_column=col+len(feilds)-1)
            cate_page_ws.cell(row=row,column=col).style = cateStyle
            cate_page_ws.cell(row=row,column=col).value = cate_name
            row+=1
            for i in range(len(feilds)):
                cate_page_ws.cell(row=row,column=col+i).style = feildStyle
                cate_page_ws.cell(row=row,column=col+i).value = feilds[i]
            days = cate_datas.keys()
            days.sort()
            
            for day  in days:
                cate_data = cate_datas[day]
                row+=1
                cate_page_ws.cell(row=row,column=1).value = day
                for j in range(len(cate_data)):
                    if col+j!=j+2:
                        cate_page_ws.cell(row=row,column=col+j).style = cate_page_ws.cell(row=row,column=j+2).style
                    cate_page_ws.cell(row=row,column=col+j).value = cate_data[j]
            row+=1
            for j in range(len(cate_data)):
                    if col+j!=j+2:
                        cate_page_ws.cell(row=row,column=col+j).style = cate_page_ws.cell(row=row,column=j+2).style
                        cate_page_ws.cell(row=row,column=col+j).value = cate_page_ws.cell(row=row,column=j+2).value
            col+=len(feilds)      
    def write_coincidence_degree_to_excel(self,wb,datas):
        shop_page_ws = wb.get_sheet_by_name(u'顾客重合度分析')
        i=2
        for data in datas:
            i+=1
            shop_page_ws['A%s'%i].style = shop_page_ws['A3'].style
            shop_page_ws['B%s'%i].style = shop_page_ws['B3'].style
            shop_page_ws['C%s'%i].style = shop_page_ws['C3'].style
            shop_page_ws['A%s'%i] = data[0]
            shop_page_ws['B%s'%i] = data[1]
            shop_page_ws['C%s'%i] = data[2]    
    def main(self,comp_groups=[],comp_ids=[],startTime = None ,endTime = None):
        if startTime == None and endTime==None:
            startTime = (datetime.date.today() - datetime.timedelta(7)).strftime('%Y-%m-%d')
            endTime = (datetime.date.today() - datetime.timedelta(1)).strftime('%Y-%m-%d')
        if not re.search('^[\d]{4}-[\d]{2}-[\d]{2}$',startTime) or not re.search('^[\d]{4}-[\d]{2}-[\d]{2}$',endTime):
            logging.error('startTime and endTime format invaild . YYYY-MM-DD')
            sys.exit()
        
        startDate  = datetime.date(int(startTime[:4]),int(startTime[5:7]),int(startTime[8:10]))
        if startDate.weekday()!=0:
            logging.error('startTime Must Be Monday')
            sys.exit()
            
        temp_file  = os.path.join(current_dir,'../templates/common/week_report_template_like_大运城.xlsx')
        output_file  = os.path.join(current_dir,'../report/客流分析报告.大运城(%s-%s).xlsx'%(startTime , endTime))
        wb = openpyxl.load_workbook(temp_file)
        

        title_page_ws = wb.get_sheet_by_name(u'封面')
        
        comp_name,shop_groups_datas,floor_groups_datas,cate_groups_datas = self.analyze_company_config(day=startTime, comp_groups=comp_groups)
        
        title_page_ws['G19'] = '公司名称：%s'%comp_name
        title_page_ws['G20'] = '统计店铺：%s家'%len(shop_groups_datas)
        title_page_ws['G21'] = '统计日期：%s至%s'%(startTime,endTime)
        
        
        days = myTimeMethod.get_slist_between_st_et(st=startTime, et=endTime, timeType=1)
        logging.info( '店铺统计 - 统计周数据')
        week_datas = self.get_week_datas(groups=shop_groups_datas.keys(), startTime=startTime, endTime=endTime)
        
        logging.info('店铺统计 - 统计周数据 - 写入excel')
        self.write_shop_datas_to_excel(wb,shop_groups_datas, week_datas)
        
        logging.info( '品类统计 - 按天统计')
        all_cate_datas = {}
        for cate , shop_groups in cate_groups_datas.items():
            all_cate_datas[cate] = {}
            for day in days:
                logging.info( 'cate: %s , day : %s '%(cate,day))
                cate_datas = self.get_day_cate_datas(groups=shop_groups, startTime = day, endTime = day)
                all_cate_datas[cate][day] = cate_datas
        logging.info( '品类统计 - 按天统计 - 写入excel')
        
        self.write_cate_datas_to_excel(wb, all_cate_datas)
        
        logging.info( '楼层统计 - 按天统计')
        
        all_floor_datas = {}
        for floor , shop_groups in floor_groups_datas.items():
            all_floor_datas[floor] = {}
            next_floor = str(int(floor)+1)
            if floor_groups_datas.has_key(next_floor):
                secondGroups = floor_groups_datas[next_floor]
            else:
                secondGroups = []
            for day in days:
                logging.info( 'floor : %s ,day : %s'%(floor,day))

                
                floor_datas  = self.get_day_floor_datas(groups=shop_groups, secondGroups=secondGroups, startTime = day, endTime = day)
                all_floor_datas[floor][day] = floor_datas
        logging.info( '楼层统计 - 按天统计 - 写入excel')
        
        self.write_floor_datas_to_excel(wb, all_floor_datas)
            
        logging.info( '重合度分析 - 周数据分析')
        coincidence_degree_datas = []
        shop_groups = shop_groups_datas.keys()
        for i in range(len(shop_groups)):
            shop_group_a = shop_groups[i]
            shop_group_a_name = shop_groups_datas[shop_group_a]['shop_name']
            for j in range(i+1,len(shop_groups)):
                
                shop_group_b = shop_groups[j]
                shop_group_b_name = shop_groups_datas[shop_group_b]['shop_name']
                shop_group_a_cnt ,shop_group_b_cnt , shop_group_a_b_cnt = self.get_shop_datas_coincidence_degree(groups=[shop_group_a], secondGroups=[shop_group_b], startTime=startTime, endTime=endTime)
                print shop_group_a_cnt ,shop_group_b_cnt , shop_group_a_b_cnt
                if shop_group_a_cnt == 0 or shop_group_b_cnt ==0:
                    shop_group_a_coincidence_degree = '-'
                    shop_group_b_coincidence_degree = '-'
                else:
                    shop_group_a_coincidence_degree = round(float(shop_group_a_b_cnt)/shop_group_a_cnt,3)
                    shop_group_b_coincidence_degree = round(float(shop_group_a_b_cnt)/shop_group_b_cnt,3)
                    
                
                coincidence_degree_datas.append([shop_group_a_name,shop_group_b_name,shop_group_a_coincidence_degree])
                coincidence_degree_datas.append([shop_group_b_name,shop_group_a_name,shop_group_b_coincidence_degree])
        logging.info( '重合度分析 - 周数据分析 - 写入excel')
        self.write_coincidence_degree_to_excel(wb, coincidence_degree_datas)
#         print self.get_week_datas(groups, startTime, endTime)
#         print self.get_day_cate_datas(groups, startTime, endTime)
#         print self.get_day_floor_datas(groups, secondGroups, startTime, endTime)
#         print self.get_shop_datas_coincidence_degree(groups, secondGroups, startTime, endTime)
#
        wb.save(output_file)
    
    
    
'''
<!-- 楼层 -->
<param name="storey_floor"  value="1" />
<!-- 品牌品类,多个品类用逗号分隔 --><param name="brand_category" value="品类1,品类2" />
'''
        
        
if __name__=="__main__":
    logging.basicConfig(level=logging.DEBUG)
    import argparse,re,datetime
    parser = argparse.ArgumentParser(description='args')
    parser.add_argument('--startTime',metavar=u"",default=None)
    parser.add_argument('--endTime',metavar=u"requests",default=None)
    parser.add_argument('--comp_id',metavar=u"comp_id in companies",default=None)
    parser.add_argument('--comp_group',metavar=u"comp_group in groups where type=0",default=None)
    args = parser.parse_args()
    startTime = args.startTime
    endTime = args.endTime
    comp_id = args.comp_id
    comp_group = args.comp_group
    startTime = '2015-11-02'
    endTime = '2015-11-08'
    comp_id = None
    comp_group = 32024171
    
    logging.basicConfig(level = logging.INFO)
    myAnalyzeDYCDatas = analyzeDYCDatas()
    myAnalyzeDYCDatas.main(comp_groups=[comp_group],startTime=startTime , endTime = endTime)
    #print myAnalyzeDYCDatas.analyze_company_config(comp_groups=[32010222])